* Import data from excel (downloaded from federalreserve.gov/aboutthefed)
* Note: each individual's party affiliation corresponds to the President who appointed them

clear
import excel name=A chair_flag=B start_date=C end_date=D party=G ///
  using "Excel/FRB Governors 1950-2023.xlsx", sheet("Members_IDChair") cellrange(a2) 

* Drop observations for Fed Chairs

drop if chair_flag=="Y"
drop chair_flag  
  
* Fill in blank values of end-date

replace end_date = dofm(1+mofd(date(c(current_date),"DMY")))-1 if missing(end_date)  
  
* Assess tenure using end-of-month incumbency

gen start_month = mofd(start_date) 
gen end_month = mofd(end_date) - cond(end_date==dofm(mofd(end_date)+1)-1, 0, 1) 
format start_month end_month %tm

* Create imbalanced monthly panel of incumbent Board members

gen nmonths = end_month + 1 - start_month
expand nmonths
sort name
by name: gen mdate = start_month + _n - 1
by name: gen tenure = (_n - 1)/12
format mdate %tm
keep if mdate >= ym(1952,1)

* Convert to annual year-end panel

gen year = year(dofm(mdate))
keep if month(dofm(mdate))==12

* Tabulate tenure statistics 

sort year tenure
by year: gen nmembers = _N
by year: egen avg_tenure = mean(tenure)
by year: egen median_tenure = median(tenure)
by year: egen min_tenure = min(tenure)
by year: egen max_tenure = max(tenure)
by year: gen ctlow_val = cond((_n==1 & _N<=3) | (_n==2 & _N>3), tenure, 0)
by year: gen cthigh_val = cond((_n==_N & _N<=3) | (_n==_N-1 & _N>3), tenure, 0)
by year: egen ctlow_tenure = max(ctlow_val)
by year: egen cthigh_tenure = max(cthigh_val)

* Tabulate party-affiliation statistics

sort year party
by year party: gen mp_id = _n
by year party: gen nparty_ = _N
keep if mp_id==1

drop name start* end* *val tenure nmonths mp_id
reshape wide nparty, i(year) j(party) string
replace nparty_D = 0 if missing(nparty_D)
replace nparty_R = 0 if missing(nparty_R)
format avg_tenure median_tenure min_tenure max_tenure %4.1f

* Export to excel spreadsheet
export excel using "Excel/FRBOG Non-Chair Year-End Characteristics 1952-2023.xlsx", firstrow(var) replace
